/*
 * @(#)UpgradeHandler4DMSModule319To320.java
 *
 * Copyright (c) 2004 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */

package com.dcivision.upgrade.handler;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.commons.dbutils.DbUtils;

import com.dcivision.framework.Utility;
import com.dcivision.upgrade.UpgradeContext;
import com.dcivision.upgrade.UpgradeExceptionHandler;

/**
 * @author      Brook.Huang
 * @company     DCIVision Limited
 * @creation    19/04/2007
 * @version     $Revision: 1.4.2.4 $
 */
public class UpgradeHandler4DMSModule319To320 extends UpgradeHandlerBase {

  public UpgradeHandler4DMSModule319To320(UpgradeContext context) throws Exception {
    super(context);

    addHandler(new LuceneIndexUpgradeHandler(context));
    addHandler(new RebuildVersionLabelHandler(context));
    addUpgradeExceptionHandler(new ResourceClean4DOCExceptionHandler());
  }

  protected void upgradeBegin() throws Exception {
    upgradeContext.logReport("<p><b>START UPGRADE PARADM DOC MODULE</b></p>\n", null);
    // Add your code here to deal with things in the front of this module's
    // upgrade.
  }

  protected void upgradeEnd() throws Exception {
  }

  ///////////////////////////////////////////////////////////////////////////
  ///
  /// Inner Classes.
  ///
  ///////////////////////////////////////////////////////////////////////////

  /*
   * Responsibility to clean the resources which is requested in upgrade of DOC module.
   * 
   * @author Brook.Huang
   */
  public class ResourceClean4DOCExceptionHandler extends UpgradeExceptionHandler {

    public ResourceClean4DOCExceptionHandler() {
      super();
    }

    public ResourceClean4DOCExceptionHandler(String exceptionClassName) {
      super(exceptionClassName);
    }

    /**
     * @see com.dcivision.upgrade.UpgradeExceptionHandler#handleException(com.dcivision.upgrade.UpgradeContext)
     */
    public void handleException(UpgradeContext context) throws Exception {
      // Set completed flag to end this upgrade for future processing.
      upgradeContext.setUpgradeCompleted(true);
    }
  }
  
  ///////////////////////////////////////////////////////////////////////////
  
  /*
   * Perform rebuild version label part of DMS module upgrade.
   */
  private class RebuildVersionLabelHandler extends UpgradeHandlerBase {
    
    static final String CURRENT_VERSION_LABEL = "CURRENT";
    static final String GENERAL_VERSION = "VERSION";
    static final String LAST_OF_CURRENT = "LAST";

    RebuildVersionLabelHandler(UpgradeContext context) throws Exception {
      super(context);
    }

    protected void upgradeBegin() throws Exception {
    }

    protected void upgradeEnd() throws Exception {
      
      // (1) Version label Upgrade.
      // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      updateVersionLabel();
    }
    
    private int updateVersionLabel() throws Exception {
    
      upgradeContext.logReport("<p><font color=blue>START REBUILD VERSION LABEL</font><br>\n", null);
      
      int count = 0;
      
      StringBuffer errorVersionDocumentIDs = new StringBuffer();
      StringBuffer topVersionIDs = new StringBuffer();
      StringBuffer lastVersionIDs = new StringBuffer();
      StringBuffer firstVersionIDs = new StringBuffer();
      
      // Phase 1 : Found out the error version label document ids.
      // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Statement errorDocumentIDsQueryStat = upgradeContext.getUpgradeConnection().createStatement();
      ResultSet errorDocumentIDsRs = null;
      
      try {
        errorDocumentIDsRs = errorDocumentIDsQueryStat.executeQuery(
            "SELECT " +
            "  DOCUMENT_ID,  " +
            "  COUNT(*) " +
            "FROM " +
            "  DMS_VERSION " +
            "WHERE " +
            "  VERSION_LABEL = '" + CURRENT_VERSION_LABEL + "' " +
            "GROUP BY " +
            "  DOCUMENT_ID HAVING COUNT(*) > 1"
            );
        
        while (!Utility.isEmpty(errorDocumentIDsRs) && errorDocumentIDsRs.next()) {
          log.info("Fetching errorVersionDocumentIDs from errorDcoumentIDsRs.");
          if (!errorDocumentIDsRs.isFirst()) {
            errorVersionDocumentIDs.append(",");
          }
          errorVersionDocumentIDs.append(errorDocumentIDsRs.getString("DOCUMENT_ID"));
        }
      } finally {
        DbUtils.closeQuietly(errorDocumentIDsRs);
        DbUtils.closeQuietly(errorDocumentIDsQueryStat);
      } 
      
      if (Utility.isEmpty(errorVersionDocumentIDs)) {
        upgradeContext.logReport(
            "<font color=blue>NO ERROR VERSION LABEL FOUND!</font><br></p>\n", null);
        
        return count; // Break if no error version label found.
      }
      
      // Phase 2 : Prepare data for update operation,
      // include topVersionIDs, lastVersionIDs, firstVersionIDs
      // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    
      Statement prepareStat = upgradeContext.getUpgradeConnection().createStatement();
      ResultSet prepareRs = null;
      
      try {
        // 2.1 : topVersionIDs
        prepareRs = prepareStat.executeQuery(
            "SELECT " +
            "  MAX(ID) AS ID " +
            "FROM " +
            "  DMS_VERSION " +
            "WHERE " +
            "  DOCUMENT_ID IN(" + errorVersionDocumentIDs.toString().trim() + ") " +
            "GROUP BY " +
            "  DOCUMENT_ID"
            );
        
        while (!Utility.isEmpty(prepareRs) && prepareRs.next()) {
          log.info("Fetching topVersionIDs from prepareRs.");
          if (!prepareRs.isFirst()) {
            topVersionIDs.append(",");
          }
          topVersionIDs.append(prepareRs.getString("ID"));
        }
        
        // 2.2 : lastVersionIDs
        prepareRs = prepareStat.executeQuery(
            "SELECT " +
            "  MAX(ID) AS ID " +
            "FROM " +
            "  DMS_VERSION " +
            "WHERE " +
            "  DOCUMENT_ID IN(" + errorVersionDocumentIDs.toString().trim() + ") " +
            "  AND ID NOT IN(" + topVersionIDs + ") " +
            "GROUP BY " +
            "  DOCUMENT_ID"
            );
        
        while (!Utility.isEmpty(prepareRs) && prepareRs.next()) {
          log.info("Fetching lastVersionIDs from prepareRs.");
          if (!prepareRs.isFirst()) {
            lastVersionIDs.append(",");
          }
          lastVersionIDs.append(prepareRs.getString("ID"));
        }
        
        // 2.3 : firstVersionIDs
        prepareRs = prepareStat.executeQuery(
            "SELECT " +
            "  MIN(ID) AS ID " +
            "FROM " +
            "  DMS_VERSION " +
            "WHERE " +
            "  DOCUMENT_ID IN(" + errorVersionDocumentIDs.toString().trim() + ") " +
            "GROUP BY " +
            "  DOCUMENT_ID"
            );
        
        while (!Utility.isEmpty(prepareRs) && prepareRs.next()) {
          log.info("Fetching firstVersionIDs from prepareRs.");
          if (!prepareRs.isFirst()) {
            firstVersionIDs.append(",");
          }
          firstVersionIDs.append(prepareRs.getString("ID"));
        }
      } finally {
        DbUtils.closeQuietly(prepareRs);
        DbUtils.closeQuietly(prepareStat);
      }
      
      // Phase 3 : Update version label.
      // Update lastVersionIDs VERSION_LABEL = LAST_OF_CURRENT.
      // Update errorDocumentIDs except topVersionIDs and lastVersionIDs
      // and firstVersionIDs VERSION_LABEL = GENERAL_VERSION.
      // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      count += updateVersionLabelByErrorDocumentIDs(
          errorVersionDocumentIDs, topVersionIDs, lastVersionIDs, firstVersionIDs);
      count += updateVersionLabelByLastVersionIDs(lastVersionIDs);
      
      upgradeContext.logReport(
          "<font color=blue>REBUILD VERSION LABEL SUCCESS (TOTAL UPDATE COUNT: "
          + count + 
          ")</font><br></p>\n", null);
      
      return count;
    }
    
    /**
     * Update version label by error document ids but not in top version id, first version id, last version id.
     */
    private int updateVersionLabelByErrorDocumentIDs(
        StringBuffer errorDocumentIDs, StringBuffer topVersionIDs,
        StringBuffer lastVersionIDs, StringBuffer firstVersionIDs) throws Exception {
      int count = 0;

      PreparedStatement stat = null;
      ResultSet rs = null;
      StringBuffer sqlStat = new StringBuffer();

      sqlStat.append(
          "SELECT " +
          "  ID, VERSION_LABEL " +
          "FROM " +
          "  DMS_VERSION " +
          "WHERE " +
          "  DOCUMENT_ID IN (" + errorDocumentIDs + ") " +
          "  AND ID NOT IN (" + topVersionIDs + ") " +
          "  AND ID NOT IN (" + firstVersionIDs + ") " +
          "  AND ID NOT IN (" + lastVersionIDs + ")"
          );

      try {
        stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_UPDATABLE);
        
        rs = stat.executeQuery();
        while (!Utility.isEmpty(rs) && rs.next()) {
          count++;
          upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
          upgradeContext.logReport(null, "VERSION_LABEL:    ");
          upgradeContext.logReport(null, "from    " + rs.getString("VERSION_LABEL") + ":    ");
          rs.updateString("VERSION_LABEL", GENERAL_VERSION);
          rs.updateRow();
          upgradeContext.logReport(null, "to    " + rs.getString("VERSION_LABEL") + ":    <br>\n");
        }
      } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stat);
      }

      return count;
    }
    
    private int updateVersionLabelByLastVersionIDs(StringBuffer lastVersionIDs) throws Exception {
      int count = 0;

      PreparedStatement stat = null;
      ResultSet rs = null;
      StringBuffer sqlStat = new StringBuffer();

      sqlStat.append(
          "SELECT " +
          "  ID, VERSION_LABEL " +
          "FROM " +
          "  DMS_VERSION " +
          "WHERE " +
          "  ID IN (" + lastVersionIDs + ") "
          );

      try {
        stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_UPDATABLE);

        rs = stat.executeQuery();
        while (!Utility.isEmpty(rs) && rs.next()) {
          count++;
          upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
          upgradeContext.logReport(null, "VERSION_LABEL:    ");
          upgradeContext.logReport(null, "from    " + rs.getString("VERSION_LABEL") + ":    ");
          rs.updateString("VERSION_LABEL", LAST_OF_CURRENT);
          rs.updateRow();
          upgradeContext.logReport(null, "to    " + rs.getString("VERSION_LABEL") + ":    <br>\n");
        }
      } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stat);
      }

      return count;
    }
  }
}
